package com.xsprite.managesys.thread;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import com.xsprite.managesys.bean.DataCell;
import com.xsprite.managesys.bean.DataRow;
import com.xsprite.managesys.bean.MySheet;
import com.xsprite.managesys.bean.XlsxFileBean;
import com.xsprite.managesys.dao.DatabaseUtil;
import com.xsprite.managesys.log.LogManager;
import com.xsprite.managesys.service.ServerConfig;

public class ImportToDatabaseThread 
//	extends Thread 
{

	/*private List<ServerConfig> serverConfigs;
	private List<XlsxFileBean> beans;
	private String userid;

	public ImportToDatabaseThread(List<ServerConfig> serverConfigs, List<XlsxFileBean> beans, String userid) {
		this.serverConfigs = serverConfigs;
		this.beans = beans;
		this.userid = userid;
	}

	@Override
	public void run() {
		for(ServerConfig serverConfig : serverConfigs){
			LogManager.getInstance().logAll("开始操作数据库:" + serverConfig.getUrl() + "--" + serverConfig.getDataName(), userid);
			Connection conn = null;
			try {
				conn = initConn(serverConfig);
				for(XlsxFileBean bean : beans){
					for(MySheet sheet : bean.getSheets()){
						if(sheet.getSheetName().indexOf("Sheet") != -1){
							continue;
						}
						
						LogManager.getInstance().logAll("开始导表: " + sheet.getSheetName(), userid);
						List<DataRow> rows = sheet.getRows();
						String updateSql = getUpdateSql(rows, sheet.getSheetName());
						PreparedStatement pstmt = conn.prepareStatement(updateSql);
						injectParamsForUpdate(rows, pstmt);
						int[] isUpdateOk = pstmt.executeBatch();
						conn.commit();
						
						List<DataRow> toInsertRows = departInsertRows(isUpdateOk, rows);
						String insertSql = getInsertSql(toInsertRows, sheet.getSheetName());
						pstmt = conn.prepareStatement(insertSql);
						pstmt.clearBatch();
						injectParamsForInsert(toInsertRows, pstmt);
						int[] isInsertOK = pstmt.executeBatch();
						conn.commit();
						logImportResult(isUpdateOk, isInsertOK);
					}
				}
			} catch (Exception ex) {
				ex.printStackTrace();
				LogManager.getInstance().logAll(ex.getMessage(), userid);
			} finally {
				try {
					if(conn != null && !conn.isClosed()){
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
					LogManager.getInstance().logAll(e.getMessage(), userid);
				}
			}
		}
	}

	private Connection initConn(ServerConfig serverConfig) throws ClassNotFoundException, SQLException {
		Connection conn;
		String url = "jdbc:mysql://" + serverConfig.getUrl() + ":" + serverConfig.getPort() + "/"
				+ serverConfig.getDataName() + "?rewriteBatchedStatements=true&characterEncoding=UTF-8";
		conn = DatabaseUtil.getInstance().getCachedConn(url, serverConfig.getUserName(), serverConfig.getPassName());
		conn.setAutoCommit(false);
		return conn;
	}
	
	private List<DataRow> departInsertRows(int[] isOK, List<DataRow> rows){
		List<DataRow> toInsertRows = new ArrayList<DataRow>();
		for(int i = 0; i < isOK.length; i++){
			if(isOK[i] <= 0){
				toInsertRows.add(rows.get(i));
			}
		}
		return toInsertRows;
	}
	
	private void logImportResult(int[] isUpdateOK, int[] isInsertOK){
		int total = isUpdateOK.length;
		int updateSuccessCounter = 0;
		for(int i = 0; i < isUpdateOK.length; i++){
			if(isUpdateOK[i] > 0){
				updateSuccessCounter++;
			}
		}
		
		int insertSuccessCounter = 0;
		for(int i = 0; i < isInsertOK.length; i++){
			if(isInsertOK[i] > 0){
				insertSuccessCounter++;
			}
		}
		LogManager.getInstance().logAll("总条目数：" + total + ", 更新条数:" 
				+ updateSuccessCounter + ", 插入条数:" + insertSuccessCounter + ", (" + (updateSuccessCounter + insertSuccessCounter) + "/" + total + ")", userid);
		LogManager.getInstance().logAll("******************************************************", userid);
	}
	
	private void injectParamsForInsert(List<DataRow> rows, PreparedStatement statment) throws NumberFormatException, SQLException{
		Iterator rowsItr = rows.iterator();
		int parameIndex = 1;
		StringBuffer logSql = new StringBuffer();
		while(rowsItr.hasNext()){
			DataRow dataRow = (DataRow) rowsItr.next();
			Iterator<DataCell> dataCells = dataRow.getDataCells().iterator();
			while(dataCells.hasNext()){
				DataCell cell = dataCells.next();
				if(cell.getType() == 1 || cell.getType() == 3){
					if(cell.getDataType() == 0){
						statment.setInt(parameIndex, Integer.parseInt(cell.getValue().toString()));
					} else if(cell.getValue() == null){
						statment.setString(parameIndex, "");
					} else {
						statment.setString(parameIndex, String.valueOf(cell.getValue()));
					}
					parameIndex++;
				}
				logSql.append(cell.getValue().toString() + "\t");
//				LogManager.getInstance().logAll(logSql.toString(), userid);
			}
			logForSql(logSql.toString(), userid);
			logSql = new StringBuffer();
			statment.addBatch();
            parameIndex = 1;
		}
	}
	
	private void injectParamsForUpdate(List<DataRow> rows, PreparedStatement pstmt) throws NumberFormatException, SQLException {
		Iterator rowsItr1 = rows.iterator();
		int parameIndex = 1;
		int testNum = 0;
		StringBuffer logSql = new StringBuffer();
		while (rowsItr1.hasNext()) {
			DataRow dataRows = (DataRow) rowsItr1.next();
			Iterator cells = dataRows.getDataCells().iterator();
			while (cells.hasNext()) {
				DataCell cell = (DataCell) cells.next();
				if ((cell.getType() == 1) || (cell.getType() == 3)) {
					if (cell.getName().equals("neednum")) {
						System.out.println(cell.getValue().toString());
						System.out.println(testNum);
						testNum++;
					}
					if (cell.getDataType() == 0) {
						pstmt.setInt(parameIndex, Integer.parseInt(cell.getValue().toString()));
					} else if (cell.getValue() == null)
						pstmt.setString(parameIndex, "");
					else {
						pstmt.setString(parameIndex, cell.getValue().toString());
					}
					parameIndex++;
				}
				if(cell.getValue().toString().equals("10019")){
					System.out.println("");
				}
				logSql.append(cell.getValue().toString() + "\t");
//				LogManager.getInstance().logAll(logSql.toString(), userid);
			}
			logForSql(logSql.toString(), userid);
			logSql = new StringBuffer();
			pstmt.setInt(parameIndex, dataRows.getRowId());
			pstmt.addBatch();
			parameIndex = 1;
		}
	}
	
	private void logForSql(String sqlVal, String userid){
		List<String> sqlVals = splitMsg(sqlVal);
		for(String msg : sqlVals){
			LogManager.getInstance().logAll(msg.toString(), userid);
		}
	}

	private List<String> splitMsg(String msg){
		String[] msges = msg.split("\n");
		List<String> result = new ArrayList<String>();
		for(String s : msges){
			if(s == null || s.length()==0){
				continue;
			}
			result.add(s);
		}
		return result;
	}
	
	private String getInsertSql(List<DataRow> rows, String tabName){
		StringBuffer insert = new StringBuffer();
	    Iterator rowsItr = rows.iterator();
	    StringBuffer values = new StringBuffer();

	    if (rowsItr.hasNext()) {
	      DataRow dataRow = (DataRow)rowsItr.next();
	      insert.append("insert into `");
	      insert.append(tabName);
	      insert.append("` ( ");
	      values.append("values(");
	      Iterator cells = dataRow.getDataCells().iterator();
	      while (cells.hasNext()) {
	        DataCell cell = (DataCell)cells.next();
	        if ((cell.getType() == 1) || (cell.getType() == 3)) {
	          insert.append("`" + cell.getName() + "`");
	          insert.append(",");
	          values.append("?,");
	        }
	      }
	      int index = insert.lastIndexOf(",");
	      insert.deleteCharAt(index);
	      insert.append(") ");
	      int vindex = values.lastIndexOf(",");
	      values.deleteCharAt(vindex);
	      values.append(")");
	      insert.append(values);
	    }
	    return insert.toString();
	}
	
	private String getUpdateSql(List<DataRow> rows, String tabName) {
		Iterator rowsItr = rows.iterator();
		StringBuffer update = new StringBuffer();
		if (rowsItr.hasNext()) {
			DataRow dataRow = (DataRow) rowsItr.next();
			update.append("update `");
			update.append(tabName);
			update.append("` set ");
			Iterator cells = dataRow.getDataCells().iterator();
			while (cells.hasNext()) {
				DataCell cell = (DataCell) cells.next();
				if ((cell.getType() == 1) || (cell.getType() == 3)) {
					update.append("`" + cell.getName() + "`");
					update.append(" = ?");
					update.append(",");
				}
			}
			int index = update.lastIndexOf(",");
			update.deleteCharAt(index);
			update.append(" where `");
			update.append("sid");
			update.append("` = ");
			update.append("?");
		}
		return update.toString();
	}*/
}
